/**
 * EditTakenProjectInfoDAO class.
 */
package pits.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pits.dto.EditTakenProjectInfoDTO;
import pits.form.EditTakenProjectForm;
import pits.util.Logger;

public class EditTakenProjectInfoDAO {

	public EditTakenProjectInfoDTO getData(String projectID, String empID) {
		EditTakenProjectInfoDTO result = new EditTakenProjectInfoDTO();
		List<String> roleNames = new ArrayList<String>();
		List<Integer> roleIDs = new ArrayList<Integer>();

		Connection conn = null;
		String query = null;
		PreparedStatement sqlStatement = null;
		ResultSet projectResultSet = null;
		ResultSet departResultSet = null;
		ResultSet roleResultSet = null;

		try {
			conn = pits.util.Connection.getConnection();

			query = "SELECT * FROM project" + " WHERE ProjectID=" + projectID + " AND isDeleted=0";
			sqlStatement = conn.prepareStatement(query);

			// new Logger().writeln(query);

			projectResultSet = sqlStatement.executeQuery();
			if (projectResultSet.next()) {
				result.setName(projectResultSet.getString("ProjectName"));
				result.setDescription(projectResultSet.getString("Description"));
				result.setPeriodFrom(projectResultSet.getString("periodFrom"));
				result.setPeriodTo(projectResultSet.getString("periodTo"));
			}

			query = "SELECT DepartName FROM project p, department d" + " WHERE p.ProjectID=" + projectID
			        + " AND p.DepartID=d.DepartID" + " AND p.isDeleted=0";
			sqlStatement = conn.prepareStatement(query);

			// new Logger().writeln(query);

			departResultSet = sqlStatement.executeQuery();
			if (departResultSet.next()) {
				result.setDepartment(departResultSet.getString("DepartName"));
			}

			query = "SELECT * FROM role WHERE isDeleted=0";
			sqlStatement = conn.prepareStatement(query);

			// new Logger().writeln(query);

			roleResultSet = sqlStatement.executeQuery();
			while (roleResultSet.next()) {
				roleNames.add(roleResultSet.getString("Role"));
				roleIDs.add(roleResultSet.getInt("RoleID"));
			}
			result.setRoleNames(roleNames);
			result.setRoleIDs(roleIDs);

			query = "SELECT * FROM role r, projectexp pe" + " WHERE pe.ProjectID=" + projectID + " AND pe.empID="
			        + empID + " AND r.RoleID=pe.RoleID" + " AND r.isDeleted=0";
			sqlStatement = conn.prepareStatement(query);
			roleResultSet = sqlStatement.executeQuery();

			// new Logger().writeln(query);

			String currentRoleName = "";
			String currentRoleID = "";
			while (roleResultSet.next()) {
				currentRoleName += ", " + roleResultSet.getString("Role");
				currentRoleID += ":" + roleResultSet.getInt("RoleID");
			}
			if (!currentRoleName.equals("")) {
				currentRoleName = currentRoleName.substring(2);
				currentRoleID = currentRoleID.substring(1);
			}
			result.setCurrentRoleName(currentRoleName);
			result.setCurrentRoleID(currentRoleID);

		} catch (SQLException sqlEx) {
			new Logger().writeln(sqlEx.toString());
		} finally {
			try {
				roleResultSet.close();
				departResultSet.close();
				projectResultSet.close();
				sqlStatement.close();
				conn.close();
			} catch (SQLException sqlEx) {
				new Logger().writeln(sqlEx.toString());
			}
		}

		return result;
	}

	public void update(EditTakenProjectForm formTakenProject) {
		Connection connection = null;
		String query = null;

		PreparedStatement sqlStatement = null;
		ResultSet roleResultSet = null;

		try {
			connection = pits.util.Connection.getConnection();

			query = "UPDATE project " + "SET Description='" + formTakenProject.getDescription() + "', "
			        + "PeriodFrom='" + formTakenProject.getPeriodFrom() + "', " + "PeriodTo='"
			        + formTakenProject.getPeriodTo() + "' " + "WHERE ProjectID=" + formTakenProject.getProjectID()
			        + " " + "AND isDeleted=0";

			// new pits.util.Logger().write(query);

			sqlStatement = connection.prepareStatement(query);
			sqlStatement.executeUpdate();

			query = "DELETE FROM projectexp " + "WHERE ProjectID=" + formTakenProject.getProjectID() + " "
			        + "AND EmpID=" + formTakenProject.getEmpID() + " " + "AND isDeleted=0";

			// new pits.util.Logger().writeln(query);

			sqlStatement = connection.prepareStatement(query);
			sqlStatement.executeUpdate();

			for (int i = 0; i < formTakenProject.getRoleNames().size(); i++) {
				query = "INSERT INTO Role (Role, isDeleted) " + "VALUE ('" + formTakenProject.getRoleNames().get(i)
				        + "', 0)";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				sqlStatement.executeUpdate();

				query = "SELECT * FROM Role" + "WHERE Role='" + formTakenProject.getRoleNames().get(i) + "' "
				        + "AND isDeleted=0";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				roleResultSet = sqlStatement.executeQuery();
				roleResultSet.next();

				query = "INSERT INTO projectexp " + "VALUES (" + formTakenProject.getProjectID() + ", "
				        + formTakenProject.getEmpID() + ", " + roleResultSet.getInt("RoleID") + ", " + "0" + ")";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				sqlStatement.executeUpdate();
			}

			for (int i = 0; i < formTakenProject.getRoleIDs().size(); i++) {

				query = "INSERT IGNORE projectexp " + "VALUES (" + formTakenProject.getProjectID() + ", "
				        + formTakenProject.getEmpID() + ", " + formTakenProject.getRoleIDs().get(i).intValue() + ", "
				        + "0" + ")";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				sqlStatement.executeUpdate();
			}
		} catch (Exception sqlEx) {
			new pits.util.Logger().writeln(sqlEx.toString());
		} finally {
			try {
				sqlStatement.close();
				connection.close();
			} catch (SQLException sqlEx) {
				new pits.util.Logger().writeln(sqlEx.toString());
			}
		}
	}
}
